/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-11-22
 * @Version: 071122.001
 * Data access object of photo model
 */
package com.ibsol.database
{
	import com.ibsol.model.Photo;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class PhotoDAO
	{
		//Database connection
		private var _tourixDBConn:SQLConnection;
		/**
		 * PhotoDAO default constructor, gets tourix database connection
		 * @param: null
		 */
		public function PhotoDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Load specific photo according to foreign id and type
		 * There are two types of information organization method.
		 * In "Package - Itinerary - Attration - Photo" mode, the foreign id here is attraction id.
		 * In "Package - Itinerary - Photo" mode, the foreign id here is itinerary id.
		 * @param: 	foreignid:int			foreign id, tour item id or itinerary id belongs to
		 * 		   	type:String				type of foreign id data
		 * 		   	resultHandler:Function	result function adapter, return photo item array collection
		 * 		  	faultHandler:Function	fault function adapt
		 * @return:	void
		 */
		public function loadPhotosByForeignData(foreignid:int, type:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM photo WHERE fid=" + foreignid + " AND type='" + type + "' AND offlineOperator != 'DEL' AND category = 'PHOTO' " + 
					" ORDER BY sequence ASC ";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Load specific video accodring to foreign id and type
		 */
		public function loadVideoByForeignDate(foreignid:int, type:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM photo WHERE fid=" + foreignid + " AND type='" + type + "' AND offlineOperator != 'DEL' AND category = 'VIDEO' " + 
					" ORDER BY sequence ASC ";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		
		public function loadRandomPhoto(foreignid:int, type:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT max(ROWID) FROM photo WHERE fid=" + foreignid + " AND type='" + type + "' AND offlineOperator != 'DEL' AND category = 'DEL' " + 
					"ORDER BY sequence ASC";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Creates a new photo into database. For different level of user, there are different options
		 * photo (id, fid, type, name, desp, sequence, offlineOperator, updateTime)
		 * @param:			photo:Photo				photo object which will be inserted
		 * 					resultHandler:Function	result handler adapter
		 * 					faultHandler:Function	fault handler adapter
		 * @return:			void
		 */
		public function insertPhoto(photo:Photo, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO photo (fid, type, name, desp, sequence, category, offlineOperator, updateTime) " + 
					"VALUES (" + photo.fid + ",'" + photo.type + "','" + photo.name + "','" + photo.desp + "'," + 
					photo.sequence + ",'PHOTO'" + ",'NEW','T')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					photo.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this, photo.id);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Insert a new video into database
		 */
		public function insertVideo(video:Photo, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO photo (fid, type, name, desp, sequence, category, offlineOperator, updateTime) " + 
					"VALUES (" + video.fid + ",'" + video.type + "','" + video.name + "','" + video.desp + "'," + 
					video.sequence + ",'VIDEO'" + ",'NEW','T')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					video.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this, video.id);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a photo record in database. For different level of user, there are different options
		 * Updates a video record into database.
		 * photo (id, fid, type, name, desp, sequence, offlineOperator)
		 * @param:	photo:Photo				photo object which will be updated
		 * 			resultHandler:Function	result handler adapter
		 * 			faultHandler:Function	fault handler adapter
		 * @return:	void
		 */
		public function updatePhoto(photo:Photo, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE photo SET fid=" + photo.fid + ",type='" + photo.type + "',name='" + photo.name + "',desp='" +
			photo.desp +  "',sequence=" + photo.sequence + ",offlineOperator='EDIT',updateTime='T' " + 
			"WHERE id=" + photo.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes a photo from database, just set offlineOperator to "DEL"
		 * @param:	id:int						the photo id, that will be deleted
		 * 			resultHandler:Function		result function adapter
		 * 			faultHandler:Function		fault function adapter
		 * @return:	void	
		 */
		public function deletePhoto(id:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE photo SET offlineOperator='DEL', updateTime='T' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a photo's sequence
		 * @param:	id:int					photo id, the photo will be updated
		 * 			sequence:int			new sequence number
		 * 			resultHandler:Function	result handler function
		 * 			faultHandler:Function	fault handler function
		 * @return:	void
		 */
		public function updatePhotoSequence(id:int, sequence:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE photo SET sequence=" + sequence + ", updateTime='T', offlineOperator = 'EDIT' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permentately delete photos from database, whose offlineOperator is set to "DEL". If user wants to delete photo, 
		 * user just updates offlineOperator to "DEL".
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function clearDeletedPhotos(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE * FROM photo WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Sets offline operator to "", this function called after synchronization with server
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return: void
		 */
		public function resetOfflineOperator(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE photo SET offlineOperator=''";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Converts database object to Photo class
		 * @param:	o:Object database return object
		 * @return:	Photo object, after conversion
		 */
		private function typeObject(o:Object):Photo
		{
			var p:Photo = new Photo();
			p.id = o.id;
			p.fid = o.fid;
			p.type = o.type;
			p.name = o.name;
			p.desp = o.desp;
			p.sequence = o.sequence;
			p.offlineOperator = o.offlineOperator;
			p.updateTime = o.updateTime;
			p.category = o.category;
			return p;
		}
		/**
		 * Converts array object to Photo array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Photo after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}